﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Oracle.DataAccess.Client;

namespace CrmApplication.Models
{
    public class SubpositionModels
	{
		#region RegionMappingSubposition
		
		public decimal Id{ get; set; }
        public string Name { get; set; }
        public decimal IdPosition{ get; set; }

		#endregion

		#region RegionOracleConnect

		private static OracleConnection connect;

        static SubpositionModels()
        {
            string connString = System.Configuration.ConfigurationManager.ConnectionStrings["HomeOrcl"].ConnectionString;
            connect = new OracleConnection(connString);
        }
		#endregion

		#region RegionSubposition
		/// <summary>
        /// select subposition list
        /// </summary>
        /// <param name="searchDomain"></param>
        /// <param name="domainId"></param>
        /// <returns></returns>
        public static List<SubpositionModels> GetSubposition(string searchSubpos,decimal subposId)
        {
            string selectSubpos = "and subposition_name like '" + searchSubpos + "%'";
            string selectString = "SELECT id_subposition,subposition_name,id_position from subposition "
                                    +" where id_position =:subposId "
                                    + selectSubpos + " order by subposition_name ";

            OracleCommand loginComm = new OracleCommand(selectString);

            loginComm.Parameters.Add(":subposId", subposId);

            loginComm.Connection = connect;

            List<SubpositionModels> result = new List<SubpositionModels>();
            SubpositionModels result1 = null;
            try
            {
                connect.Open();
                using (OracleDataReader sdr = loginComm.ExecuteReader())
                {
                    while (sdr.Read())
                    {
                        result1 = new SubpositionModels();
                        result1.Id = sdr.GetDecimal(0);
                        result1.Name = sdr.GetString(1);
                        result1.IdPosition= sdr.GetDecimal(2);
                        result.Add(result1);
                    }
                }
            }
            finally
            {
                connect.Close();
            }
            return result;
        }

        /// <summary>
        /// check if subposition exists
        /// </summary>
        /// <param name="subposition"></param>
        /// <returns></returns>
        public static SubpositionModels CheckSubposition(SubpositionModels subpos)
        {
            string selectString = "select id_subposition,subposition_name from subposition"
                                    + " where subposition_name=:subpositionName and id_position=:positionId";

            OracleCommand loginComm = new OracleCommand(selectString);
            loginComm.Connection = connect;

            loginComm.Parameters.Add(":subpositionName", subpos.Name);
            loginComm.Parameters.Add(":positionId", subpos.IdPosition);

            SubpositionModels result = null;

            try
            {
                connect.Open();
                using (OracleDataReader sdr = loginComm.ExecuteReader())
                {
                    while (sdr.Read())
                    {
                        result = new SubpositionModels();
                        result.Id = sdr.GetDecimal(0);
                        result.Name = sdr.GetString(1);
                        result.IdPosition = sdr.GetDecimal(2);
                    }
                }
            }
            finally
            {
                connect.Close();
            }
            return result;
        }

        /// <summary>
        /// insert new subposition
        /// </summary>
        /// <param name="subdomainName"></param>
        /// <param name="domainId"></param>
        public static void AddSubPosition(SubpositionModels subpos)
        {
            string insert = "INSERT INTO subposition(subposition_name,id_position) VALUES (:subposName,:posId)";

            OracleCommand usercomm = new OracleCommand(insert);

            usercomm.Parameters.Add(":subposName", subpos.Name);
            usercomm.Parameters.Add(":posId", subpos.IdPosition);

            usercomm.Connection = connect;
            try
            {
                connect.Open();
                usercomm.ExecuteNonQuery();
            }
            finally
            {
                connect.Close();
            }
        }

        /// <summary>
        /// creat subposition array for json
        /// </summary>
        /// <param name="searchDomain"></param>
        /// <param name="parentId"></param>
        /// <returns></returns>
        public static SubpositionContainer GetSubposArray(string searchPos, decimal positionId)
        {
            SubpositionModels[] data = new SubpositionModels[20];
            data = SubpositionModels.GetSubposition(searchPos, positionId).ToArray();

            SubpositionContainer container = new SubpositionContainer()
            {
                Records = data,
                Count = data.Length
            };

            return container;
		}
		#endregion
	}

    public class SubpositionContainer
    {
        public SubpositionModels[] Records { get; set; }
        public int Count { get; set; }
    }
}